-- @owner: cr13
-- @date: 2025/3/27
-- @testpoint:视图引用自定义函数，引用在子查询语句中，部分合理报错
--step1:创建自定义函数;expect:成功
drop FUNCTION if exists func_ddl0011;
CREATE FUNCTION func_ddl0011(n1 IN NUMBER, n2 IN NUMBER)
RETURNS NUMBER AS $$
DECLARE
 sum_result NUMBER;
BEGIN
 sum_result := n1 + n2;
 RETURN sum_result;
END;
$$ LANGUAGE plpgsql;
/
--step2:创建表;expect:成功
drop table if exists tb_ddl0011;
CREATE TABLE tb_ddl0011 (
 smallint_col SMALLINT,
 integer_col INTEGER,
 bigint_col BIGINT,
 decimal_col DECIMAL(10, 2),
 numeric_col NUMERIC(10, 2),
 real_col REAL,
 double_col DOUBLE PRECISION,
 serial_col SERIAL PRIMARY KEY,
 bigserial_col BIGSERIAL UNIQUE,
 char_col CHAR(10),
 varchar_col VARCHAR(255),
 text_col TEXT,
 bytea_col BYTEA,
 bit_col BIT(8),
 bitvar_col BIT VARYING(32),
 bool_col BOOLEAN,
 date_col DATE,
 time_col TIME WITHOUT TIME ZONE,
 timetz_col TIME WITH TIME ZONE,
 timestamp_col TIMESTAMP WITHOUT TIME ZONE,
 timestamptz_col TIMESTAMP WITH TIME ZONE,
 interval_col INTERVAL,
 json_col JSON,
 jsonb_col JSONB,
 cidr_col CIDR,
 inet_col INET,
 macaddr_col MACADDR,
 uuid_col UUID ,
 xml_col XML,
 int_array_col INTEGER[],
 text_array_col TEXT[]

);
INSERT INTO tb_ddl0011 (
 smallint_col, integer_col, bigint_col, decimal_col, numeric_col,
 real_col, double_col, char_col, varchar_col, text_col,
 bytea_col, bit_col, bitvar_col, bool_col,
 date_col, time_col, timetz_col, timestamp_col, timestamptz_col, interval_col,
 json_col, jsonb_col, cidr_col, inet_col, macaddr_col,
 xml_col, int_array_col, text_array_col
) VALUES (
 32767, 2147483647, 9223372036854775807, 12345.67, 12345.67,
 123.45, 123.456789, 'abcdefg', 'varchar value', 'This is a text value.',
 E'\\x1234567890abcdef', B'10101010', B'1100111', true,
 '2023-10-01', '12:34:56', '12:34:56+08', '2023-10-01 12:34:56', '2023-10-01 12:34:56+08', INTERVAL '1 day',
 '{"key": "value"}', '{"key": "value"}'::jsonb,
 '192.168.1', '192.168.1.1', '00:1a:2b:3c:4d:5e',
 '<root><child>data</child></root>',
 ARRAY[1, 2, 3],
 ARRAY['a', 'b', 'c']
);
--step3:创建视图;expect:成功
drop view if exists v_ddl0011;
create view v_ddl0011 as select *,(select func_ddl0011(1,32766)) from tb_ddl0011;
--step4:查询视图;expect:成功
select * from v_ddl0011;
--step5:删除函数;expect:成功
drop fUNCTION func_ddl0011;
--step6:查询视图状态;expect:无效
select valid from pg_object where object_oid='v_ddl0011'::regclass;
--step7:查询视图数据;expect:报错
select * from v_ddl0011;
--step8:查询视图定义;expect:有告警
SELECT table_name, view_definition
FROM information_schema.views
WHERE table_name = 'v_ddl0011';
--step9:重新创建函数，和原始函数一样;expect:成功
CREATE FUNCTION func_ddl0011(n1 IN NUMBER, n2 IN NUMBER)
RETURNS NUMBER AS $$
DECLARE
 sum_result NUMBER;
BEGIN
 sum_result := n1 + n2;
 RETURN sum_result;
END;
$$ LANGUAGE plpgsql;
/
--step10:查询视图状态;expect:无效
select valid from pg_object where object_oid='v_ddl0011'::regclass;
--step11:查询视图数据;expect:成功
select * from v_ddl0011;
--step12:查询视图状态;expect:有效
select valid from pg_object where object_oid='v_ddl0011'::regclass;
--step13:清理环境;;expect:成功
drop view if exists v_ddl0011;
drop fUNCTION func_ddl0011;
drop table if exists tb_ddl0011;